package org.yihang.db;

import org.yihang.db.ColumnHandler.ColumnHandlers;
import org.yihang.db.ColumnHandler.Converter;
import org.yihang.db.ColumnHandler.Converters;

import javax.sql.DataSource;
import java.io.InputStream;
import java.io.Serializable;
import java.sql.*;
import java.util.*;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * 提高版的jdbc工具类
 *
 * @author yihang
 */
public class Jdbc implements AutoCloseable {
    private static ThreadLocal<Connection> conns = new ThreadLocal<Connection>();
    private volatile boolean debug = false;
    private ConnectionCreator connectionCreator;
    private Dialect dialect;

    private Jdbc() {

    }

    /**
     * 释放资源，包括ResultSet与Statement
     */
    private void release(Object... resources) {
        if (resources != null) {
            for (Object r : resources) {
                if (r instanceof ResultSet) {
                    ResultSet rs = (ResultSet) r;
                    try {
                        rs.close();
                    } catch (SQLException e) {
                    }
                }
            }
            for (Object r : resources) {
                if (r instanceof Statement) {
                    Statement stmt = (Statement) r;
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                    }
                }
            }
        }
    }

    @Override
    public void close() {
        Connection conn = conns.get();
        if (conn != null) {
            try {
                conn.setAutoCommit(true);
                conn.close();
                if (debug) {
                    System.out.println("close connection: " + conn);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conns.remove();
        }
    }

    private Connection getConnection() {
        Connection conn = conns.get();
        if (conn == null) {
            conn = connectionCreator.newConneciton();
            conns.set(conn);
        }
        return conn;
    }


    private void rollback(Connection conn) {
        if (conn != null) {
            try {
                conn.rollback();
                conn.setAutoCommit(true);
            } catch (SQLException e1) {
            }
            if (debug) {
                System.out.println("rollback transactional: " + conn);
            }
        }
    }

    private void commit(Connection conn) {
        if (conn != null) {
            try {
                conn.commit();
                conn.setAutoCommit(true);
            } catch (SQLException e1) {
            }
            if (debug) {
                System.out.println("commit transactional: " + conn);
            }
        }
    }

    /**
     * 执行事务操作，不需要返回值
     *
     * @param consumers 回调函数 参数为jdbc对象，函数没有返回值
     */
    @SafeVarargs
    public final void transaction(Consumer<Jdbc>... consumers) {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
            Arrays.stream(consumers).forEach(c -> c.accept(this));
            commit(conn);
        } catch (Exception e) {
            rollback(conn);
            throw new RuntimeException(e);
        } finally {
            close();
        }
    }

    /**
     * 执行事务操作，需要返回值
     *
     * @param functions 回调函数 参数为jdbc对象，函数有返回值
     * @return 函数返回值的集合
     */
    @SafeVarargs
    public final List<Object> transaction(Function<Jdbc, Object>... functions) {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
            List<Object> collect = Arrays.stream(functions).map(f -> f.apply(this)).collect(Collectors.toList());
            commit(conn);
            return collect;
        } catch (Exception e) {
            rollback(conn);
            throw new RuntimeException(e);
        } finally {
            close();
        }
    }

    /**
     * 通用update,delete,insert方法
     *
     * @param sql    SQL语句
     * @param params SQL需要的?参数
     * @return 影响行数
     */
    public int update(String sql, Object... params) {
        Connection conn;
        PreparedStatement psmt = null;
        try {
            conn = getConnection();
            psmt = conn.prepareStatement(sql);
            prepare(psmt, params);
            log(sql, params);
            return psmt.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            release(psmt);
        }
    }

    /**
     * 通用insert方法(需要获得主键值时使用)
     *
     * @param sql      SQL语句
     * @param pkColumn 主键列的名字
     * @param params   SQL需要的?参数
     * @return 影响行数
     */
    public int insert(String sql, String pkColumn, Object... params) {
        Connection conn;
        PreparedStatement psmt = null;
        ResultSet rs;
        try {
            conn = getConnection();
            psmt = conn.prepareStatement(sql, new String[]{pkColumn});
            prepare(psmt, params);
            log(sql, params);
            psmt.executeUpdate();
            rs = psmt.getGeneratedKeys();
            rs.next();
            return rs.getInt(1);

        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            release(psmt);
        }
    }

    /**
     * 给preparedStatement中所有?占位符赋值
     *
     * @param psmt   语句对象
     * @param params 值数组
     * @throws SQLException 可能出现的jdbc异常
     */
    private void prepare(PreparedStatement psmt, Object... params) throws SQLException {
        if (params != null) {
            int i = 1;
            for (Object o : params) {
                if (o == null) {
                    ColumnHandlers.DefaultHandler.toJdbc(psmt, o, i);
                } else {
                    ColumnHandlers.getHandler(o.getClass()).toJdbc(psmt, o, i);
                }
                i++;
            }
        }
    }

    /**
     * 通用查询方法，可以用来查询单个对象或单值
     *
     * @param <T>       泛型
     * @param beanClass 要封装的对象类型
     * @param sql       SQL语句
     * @param params    SQL语句需要的?参数
     * @return 单个对象
     */
    public <T> T queryOne(Class<T> beanClass, String sql, Object... params) {
        Connection conn;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            psmt = conn.prepareStatement(sql);
            prepare(psmt, params);
            log(sql, params);
            rs = psmt.executeQuery();
            if (rs.next()) {
                if (ColumnHandlers.support(beanClass)) {
                    return (T) ColumnHandlers.process(beanClass, rs, 1);
                } else {
                    return (T) BeanHandler.process(beanClass, rs);
                }
            } else {
                return null;
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            release(rs, psmt);
        }
    }

    /**
     * 通用查询方法，可以用来查询对象集合或值集合
     *
     * @param <T>       泛型
     * @param beanClass 要封装的对象类型
     * @param sql       SQL语句
     * @param params    SQL语句需要的?参数
     * @return 对象集合
     */
    public <T> List<T> queryList(Class<T> beanClass, String sql, Object... params) {
        Connection conn;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            psmt = conn.prepareStatement(sql);
            prepare(psmt, params);
            log(sql, params);
            rs = psmt.executeQuery();
            List<T> list = new ArrayList<T>();
            while (rs.next()) {
                if (ColumnHandlers.support(beanClass)) {
                    list.add((T) ColumnHandlers.process(beanClass, rs, 1));
                } else {
                    list.add((T) BeanHandler.process(beanClass, rs));
                }
            }
            return list;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            release(rs, psmt);
        }
    }

    private void log(String sql, Object[] params) {
        if (debug) {
            System.out.println("SQL: [" + sql + "]");
            System.out.println("SQL params: " + Arrays.toString(params));
        }
    }

    public <T, K extends Serializable> Map<K, T> queryIdMap(
            Class<K> keyClass, Class<T> valueClass, String sql, Object... params) {
        Connection conn;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            psmt = conn.prepareStatement(sql);
            prepare(psmt, params);
            log(sql, params);
            rs = psmt.executeQuery();
            Map<K, T> map = new LinkedHashMap<K, T>();
            while (rs.next()) {
                if (ColumnHandlers.support(valueClass) && ColumnHandlers.support(keyClass)) {
                    K key = (K) ColumnHandlers.process(keyClass, rs, 1);
                    T value = (T) ColumnHandlers.process(valueClass, rs, 2);
                    map.put(key, value);
                } else {
                    throw new RuntimeException(
                            "only support two column selection(column 1 for key, column 2 for value)");
                }
            }
            return map;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            release(rs, psmt);
        }
    }

    /**
     * 通用查询方法，用来执行分页查询(Oracle)
     *
     * @param <T>       泛型
     * @param beanClass 要封装的对象类型
     * @param sql       SQL语句
     * @param pageNum   页号
     * @param pageSize  页面大小
     * @param params    SQL语句需要的?参数
     * @return 分页后集合
     */
    public <T> List<T> queryList(Class<T> beanClass, int pageNum, int pageSize, String sql, Object... params) {
        Object[] prepare = dialect.prepare(pageNum, pageSize, sql, params);
        return queryList(beanClass, (String) prepare[0], (Object[]) prepare[1]);
    }

    interface ConnectionCreator {
        /**
         * 获取一个新的数据库连接
         *
         * @return 数据库连接
         */
        Connection newConneciton();
    }

    interface QueryForPage {
        /**
         * 准备分页需要的数据
         *
         * @param pageNum  页号
         * @param pageSize 页面大小
         * @param sql      SQL语句
         * @param params   SQL语句需要的?参数
         * @return 一个长度为2的Object数组，其中[0]表示分页SQL，[1]表示添加了分页信息的参数
         */
        Object[] prepare(int pageNum, int pageSize, String sql, Object... params);
    }

    public enum Dialect implements QueryForPage {
        MySQL {
            @Override
            public Object[] prepare(int pageNum, int pageSize, String sql, Object... params) {
                String subsql = sql + " limit ?, ?";
                Object[] p;
                if (params == null || params.length == 0) {
                    p = new Object[2];
                    p[0] = (pageNum - 1) * pageSize;
                    p[1] = pageSize;
                } else {
                    p = new Object[params.length + 2];
                    System.arraycopy(params, 0, p, 0, params.length);
                    p[params.length] = (pageNum - 1) * pageSize;
                    p[params.length + 1] = pageSize;
                }
                return new Object[]{subsql, p};
            }
        }, Oracle {
            @Override
            public Object[] prepare(int pageNum, int pageSize, String sql, Object... params) {
                String subsql = "select * from (select rownum r, a.* from (" + sql + ") a where rownum <=?) where r > ?";
                Object[] p;
                if (params == null || params.length == 0) {
                    p = new Object[2];
                    p[0] = pageNum * pageSize;
                    p[1] = (pageNum - 1) * pageSize;
                } else {
                    p = new Object[params.length + 2];
                    System.arraycopy(params, 0, p, 0, params.length);
                    p[params.length] = pageNum * pageSize;
                    p[params.length + 1] = (pageNum - 1) * pageSize;
                }
                return new Object[]{subsql, p};
            }
        };
    }

    public static class Builder {
        private static final Builder BUILDER = new Builder();
        private static Jdbc INSTANCE = new Jdbc();
        private static final Object LOCK = new Object();

        static {
            Converters.init();
            ColumnHandlers.init();
            INSTANCE.dialect = Dialect.MySQL;
        }

        private Builder() {
        }

        public static Builder direct() {
            synchronized (LOCK) {
                if (INSTANCE.connectionCreator == null) {
                    try (InputStream is = Jdbc.class.getResourceAsStream("/config.properties")) {
                        final Properties p = new Properties();
                        p.load(is);
                        String driver = p.getProperty("jdbc.driver");
                        Class.forName(driver);
                        INSTANCE.connectionCreator = () -> {
                            try {
                                String url = p.getProperty("jdbc.url");
                                String username = p.getProperty("jdbc.username");
                                String password = p.getProperty("jdbc.password");
                                Connection conn = DriverManager.getConnection(url, username, password);
                                if (INSTANCE.debug) {
                                    System.out.println("open connection: " + conn);
                                }
                                return conn;
                            } catch (Exception e) {
                                throw new RuntimeException("error when getConnection", e);
                            }
                        };
                    } catch (Throwable e) {
                        // 应用程序初始化错误
                        throw new ExceptionInInitializerError(e);
                    }
                }
            }
            return BUILDER;
        }

        public static Builder dataSource(DataSource dataSource) {
            synchronized (LOCK) {
                if (INSTANCE.connectionCreator == null) {
                    INSTANCE.connectionCreator = () -> {
                        try {
                            return dataSource.getConnection();
                        } catch (SQLException e) {
                            throw new RuntimeException("error when getConnection", e);
                        }
                    };
                }
            }
            return BUILDER;
        }

        public static Builder debug(boolean debug) {
            INSTANCE.debug = debug;
            return BUILDER;
        }

        public static Builder debug(Dialect dialect) {
            INSTANCE.dialect = dialect;
            return BUILDER;
        }

        public static Builder register(Class<?> clazz, Converter converter) {
            Converters.registerConverter(clazz, converter);
            return BUILDER;
        }

        public static Builder register(Class<?> clazz, ColumnHandler handler) {
            ColumnHandlers.registerHandler(clazz, handler);
            return BUILDER;
        }

        public static Jdbc get() {
            return INSTANCE;
        }
    }


}